package treazy.ultimate.stats;

import java.util.ArrayList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper {
	
	// All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 20;
 
    // Database Name
    private static final String DATABASE_NAME = "UltiSystemsManager";

    // Table names
    private static final String TABLE_TEAMS = "teams";
    private static final String TABLE_PLAYERS = "players";
    private static final String TABLE_ROSTERS = "roster";
    private static final String TABLE_GAMES = "games";
 
    // Teams table column names
    private static final String KEY_TEAMS_ID = "_id";
    private static final String KEY_TEAMS_NAME = "name";
    private static final String KEY_TEAMS_WINS = "wins";
    private static final String KEY_TEAMS_LOSSES = "losses";
    private static final String KEY_TEAMS_PF = "points_for";
    private static final String KEY_TEAMS_PA = "points_against";

    // Players table column names
    private static final String KEY_PLAYERS_ID = "_id";
    private static final String KEY_PLAYERS_NAME = "name";
    private static final String KEY_PLAYERS_GOALS = "goals";
    private static final String KEY_PLAYERS_ASSISTS = "assists";
    private static final String KEY_PLAYERS_POINTSPLAYED = "points_played";
    private static final String KEY_PLAYERS_TURNS = "turns";
    private static final String KEY_PLAYERS_NICKNAME = "nickname";
    
    //Roster table column names
    private static final String KEY_ROSTER_ID = "_id";
    private static final String KEY_ROSTER_TEAMNAME = "teamname";
    private static final String KEY_ROSTER_PLAYERNAME = "playername";
    private static final String KEY_ROSTER_PLAYERID = "playerid";
    
    // Games table column names
    private static final String KEY_GAME_ID = "_id";
    private static final String KEY_GAME_TEAM1 = "team1";
    private static final String KEY_GAME_TEAM2 = "team2";
    private static final String KEY_GAME_TEAM1POINTS = "team1points";
    private static final String KEY_GAME_TEAM2POINTS = "team2points";
    private static final String KEY_GAME_TOURNEY = "tournament";
    //ADD: more stats

    /** Database Handler Constructor
     * 
     * @param context Usually "this"
     */
    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    
    //Creating tables
	@Override
	public void onCreate(SQLiteDatabase db) {
		String CREATE_TEAMS_TABLE = "CREATE TABLE " + TABLE_TEAMS + "("
                + KEY_TEAMS_ID + " INTEGER PRIMARY KEY," + KEY_TEAMS_NAME + " TEXT,"
                + KEY_TEAMS_WINS + " INTEGER," + KEY_TEAMS_LOSSES + " INTEGER," 
                + KEY_TEAMS_PF + " INTEGER," + KEY_TEAMS_PA + " INTEGER" + ")";
        db.execSQL(CREATE_TEAMS_TABLE);
        
        String CREATE_PLAYERS_TABLE = "CREATE TABLE " + TABLE_PLAYERS + "("
                + KEY_PLAYERS_ID + " INTEGER PRIMARY KEY," + KEY_PLAYERS_NAME + " TEXT,"
                + KEY_PLAYERS_GOALS + " INTEGER," + KEY_PLAYERS_ASSISTS + " INTEGER," 
                + KEY_PLAYERS_POINTSPLAYED + " INTEGER," + KEY_PLAYERS_TURNS + " INTEGER," 
                + KEY_PLAYERS_NICKNAME + " TEXT" + ")";
        db.execSQL(CREATE_PLAYERS_TABLE);
        
        String CREATE_ROSTER_TABLE = "CREATE TABLE " + TABLE_ROSTERS + "("
                + KEY_ROSTER_ID + " INTEGER PRIMARY KEY," + KEY_ROSTER_TEAMNAME + " TEXT,"
                + KEY_ROSTER_PLAYERNAME + " TEXT," + KEY_ROSTER_PLAYERID + " INTEGER" + ")";
        db.execSQL(CREATE_ROSTER_TABLE);
        
        String CREATE_GAMES_TABLE = "CREATE TABLE " + TABLE_GAMES + "("
        		+ KEY_GAME_ID + " INTEGER PRIMARY KEY," + KEY_GAME_TEAM1 + " TEXT,"
        		+ KEY_GAME_TEAM2 + " TEXT," + KEY_GAME_TEAM1POINTS + " INTEGER," 
        		+ KEY_GAME_TEAM2POINTS + " INTEGER," + KEY_GAME_TOURNEY + " TEXT" + ")";
        db.execSQL(CREATE_GAMES_TABLE);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TEAMS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PLAYERS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ROSTERS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_GAMES);
        
        // Create tables again
        onCreate(db);

	}
	
 
    /** Adding a new team to the database
     * 
     * @param team The Team object to be added
     */
    void addTeam(Team team) {
        SQLiteDatabase db = this.getWritableDatabase();
 
        ContentValues values = new ContentValues();
        values.put(KEY_TEAMS_NAME, team.getName()); // Team name
        values.put(KEY_TEAMS_WINS, team.getWins()); // Team wins
        values.put(KEY_TEAMS_LOSSES, team.getLosses()); // Team losses
        values.put(KEY_TEAMS_PF, team.getPointsFor()); // Team points for
        values.put(KEY_TEAMS_PA, team.getPointsAgainst()); // Team points against
        
        
        // Inserting Row
        db.insert(TABLE_TEAMS, null, values);
        db.close(); // Closing database connection
    }
    
    /** Getting a single team
     * 
     * @param teamName The name of the team to retrieve
     * @return Team object
     */
    Team getTeam(String teamName) {
        SQLiteDatabase db = this.getReadableDatabase();
 
        Cursor cursor = db.query(TABLE_TEAMS, new String[] { KEY_TEAMS_NAME,
        		KEY_TEAMS_WINS, KEY_TEAMS_LOSSES, KEY_TEAMS_PF, KEY_TEAMS_PA }, KEY_TEAMS_NAME + "=?",
                new String[] { teamName }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();
 
        Team team = new Team(teamName, cursor.getInt(2),
                cursor.getInt(3), cursor.getInt(4), cursor.getInt(5));
        team.setRoster(getRoster(teamName));
        db.close();
        
        return team;
    }
    
    /** Getting all teams from the database
     * 
     * @return An ArrayList of all Team objects
     */
    public ArrayList<Team> getAllTeams() {
        ArrayList<Team> teamList = new ArrayList<Team>();
        // Select All Query
        String selectQuery = "SELECT * FROM " + TABLE_TEAMS;
 
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
 
        // Looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Team team = new Team();
                team.set_id(cursor.getInt(0));
                team.setName(cursor.getString(1));
                team.setWins(cursor.getInt(2));
                team.setLosses(cursor.getInt(3));
                team.setPointsFor(cursor.getInt(4));
                team.setPointsAgainst(cursor.getInt(5));
                team.setRoster(getRoster(cursor.getString(1)));

                teamList.add(team);
            } while (cursor.moveToNext());
        }
        
        db.close();
        // return team list
        return teamList;
    }
 
    /** Updating a single team in the database
     * 
     * @param team the team to be updated
     *
     */
    public void updateTeam(Team team) {
        SQLiteDatabase db = this.getWritableDatabase();
 
        ContentValues values = new ContentValues();
        values.put(KEY_TEAMS_NAME, team.getName()); // Team name
        values.put(KEY_TEAMS_WINS, team.getWins()); // Team wins
        values.put(KEY_TEAMS_LOSSES, team.getLosses()); // Team losses
        values.put(KEY_TEAMS_PF, team.getPointsFor()); // Team points for
        values.put(KEY_TEAMS_PA, team.getPointsAgainst()); // Team points against

        db.update(TABLE_TEAMS, values, KEY_TEAMS_NAME + " = ?",
                new String[] { team.getName() });
        
        db.close();
    }
 
    /** Deleting a single team from the database
     * 
     * @param team The team to be deleted
     */
    public void deleteTeam(Team team) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_ROSTERS, KEY_ROSTER_TEAMNAME + " = ?", new String[] { team.getName() });
        db.delete(TABLE_TEAMS, KEY_TEAMS_NAME + " = ?", new String[] { team.getName() });
        db.close();
    }
 
    /** Getting the total number of teams in the database
     * 
     * @return The total number of teams
     */
    public int getTeamsCount() {
        String countQuery = "SELECT  * FROM " + TABLE_TEAMS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int count = cursor.getCount();
        
        cursor.close();
        db.close();
        
        return count;
    }

    /** Adding a new player to the database
     * 
     * @param player The Player object to be added
     */
    void addPlayer(Player player) {
        SQLiteDatabase db = this.getWritableDatabase();
 
        ContentValues values = new ContentValues();
        values.put(KEY_PLAYERS_NAME, player.getName()); 
        values.put(KEY_PLAYERS_GOALS, player.getGoals());
        values.put(KEY_PLAYERS_ASSISTS, player.getAssists());
        values.put(KEY_PLAYERS_POINTSPLAYED, player.getPointsPlayed());
        values.put(KEY_PLAYERS_TURNS, player.getTurns());
        values.put(KEY_PLAYERS_NICKNAME, player.getNickname());
        
        db.insert(TABLE_PLAYERS, null, values);
        
        /* ADD: query players by name, nickname, and (maybe) current team, because two players with the same 
         * name will have different nicknames
         */
        Cursor cursor = db.query(TABLE_PLAYERS, new String [] { KEY_PLAYERS_ID }, KEY_PLAYERS_NAME + "=?", new String[] { player.getName() }, 
        		null, null, null);
        
        values.clear();
        
        values.put(KEY_ROSTER_TEAMNAME, player.getCurrentTeam());
        values.put(KEY_ROSTER_PLAYERNAME, player.getName());
        if(cursor.moveToFirst()) values.put(KEY_ROSTER_PLAYERID, cursor.getInt(0));
        else values.put(KEY_ROSTER_PLAYERID, -1);
        
        db.insert(TABLE_ROSTERS, null, values);
        
        // Closing database connection
        db.close();
    }
    
    /** Getting a single player from the database
     * 
     * @param playerName The name of the player to be retrieved
     * @param currentTeam The team of the player being retrieved
     * @return Player object
     */
    Player getPlayer(String playerName, String currentTeam) {
        SQLiteDatabase db = this.getReadableDatabase();
 
        Cursor cursor = db.query(TABLE_PLAYERS, new String[] { KEY_PLAYERS_NAME, KEY_PLAYERS_GOALS, KEY_PLAYERS_ASSISTS, 
        		KEY_PLAYERS_POINTSPLAYED, KEY_PLAYERS_TURNS, KEY_PLAYERS_NICKNAME }, KEY_PLAYERS_NAME + "=?",
                new String[] { playerName }, null, null, null, null);
        if (cursor != null) cursor.moveToFirst();
        int goals = cursor.getInt(1);
        int assists = cursor.getInt(2);
        int pointsplayed = cursor.getInt(3);
        int turns = cursor.getInt(4);
        String nickname = cursor.getString(5);
 
        Player player = new Player(playerName, goals, assists, pointsplayed, turns, nickname, currentTeam);
        
        cursor.close();
        db.close();

        return player;
    }
    
    //Getting All players
    public ArrayList<Player> getRoster(String teamName) {
        ArrayList<Player> playerList = new ArrayList<Player>();
  
        SQLiteDatabase db = this.getReadableDatabase();
        
        // Query to get player names/ids
        Cursor cursorPlayers = db.query(TABLE_ROSTERS, new String[] { KEY_ROSTER_PLAYERNAME, KEY_ROSTER_PLAYERID}, KEY_ROSTER_TEAMNAME + "=?",
                new String[] { teamName }, null, null, null);

        // Looping through all players
        if (cursorPlayers.moveToFirst()) {
            for (int i = 0; i < cursorPlayers.getCount(); i++) {
            	
            	int playerId = cursorPlayers.getInt(1);
            	String sPlayerId = Integer.toString(playerId);
            	// Query to get player data
            	Cursor cursorPlayerData = db.query(TABLE_PLAYERS, new String [] {KEY_PLAYERS_GOALS, KEY_PLAYERS_ASSISTS, 
            			KEY_PLAYERS_POINTSPLAYED, KEY_PLAYERS_TURNS, KEY_PLAYERS_NICKNAME  }, KEY_PLAYERS_ID + "=?", new String[] { sPlayerId }, 
            			null, null, null);
            	            	
            	if (cursorPlayerData.moveToFirst()) {
            		String name = cursorPlayers.getString(0);
            		int goals = cursorPlayerData.getInt(0);
            		int assists = cursorPlayerData.getInt(1);
            		int pointsplayed = cursorPlayerData.getInt(2);
            		int turns = cursorPlayerData.getInt(3);
            		String nickname = cursorPlayerData.getString(4);
            		
            		playerList.add(new Player(name, goals, assists, pointsplayed, turns, nickname, teamName));
            	}
            	cursorPlayers.moveToNext();
            }
        }
        
        cursorPlayers.close();
        db.close();

        return playerList;
    }
 
    // Updating single player
    public int updatePlayer(Player player) {
        SQLiteDatabase db = this.getWritableDatabase();
 
        ContentValues values = new ContentValues();
        values.put(KEY_PLAYERS_NAME, player.getName());
        values.put(KEY_PLAYERS_GOALS, player.getGoals());
        values.put(KEY_PLAYERS_ASSISTS, player.getAssists());
        values.put(KEY_PLAYERS_POINTSPLAYED, player.getPointsPlayed());
        values.put(KEY_PLAYERS_TURNS, player.getTurns()); 
        values.put(KEY_PLAYERS_NICKNAME, player.getNickname());
        
        db.close();
        // updating row
        return db.update(TABLE_PLAYERS, values, KEY_PLAYERS_NAME + " = ?",
                new String[] { player.getName() });
    }
 
    // Deleting single player
    public void deletePlayer(Player player) {
        SQLiteDatabase db = this.getWritableDatabase();
        
        db.delete(TABLE_PLAYERS, KEY_PLAYERS_NAME + " = ?",
                new String[] { player.getName() });
        db.delete(TABLE_ROSTERS, KEY_ROSTER_PLAYERNAME + " = ?",
                new String[] { player.getName() });
        
        db.close();
    }
 
    // Getting player count
    public int getPlayerCount(String teamName) {
        SQLiteDatabase db = this.getReadableDatabase();
        
        String countQuery = "SELECT  * FROM " + TABLE_ROSTERS + "WHERE " + KEY_ROSTER_TEAMNAME + "=?";
        Cursor cursor = db.rawQuery(countQuery, new String[] { teamName });
        int count = cursor.getCount();
        
        cursor.close();
        db.close();
        
        return count;
    }
    
    // Add a new game
    public void addGame(Game game) {
    	SQLiteDatabase db = getWritableDatabase();
    	
    	ContentValues values = new ContentValues();
    	
        values.put(KEY_GAME_TEAM1, game.getTeam1().getName()); // Team1 name
        values.put(KEY_GAME_TEAM2, game.getTeam2().getName()); // Team2 name
        values.put(KEY_GAME_TEAM1POINTS, game.getTeam1Points()); // Team1 points
        values.put(KEY_GAME_TEAM2POINTS, game.getTeam2Points()); // Team2 points
        
        
        // Inserting Row
        db.insert(TABLE_GAMES, null, values);
        db.close();
    }
    
    // Getting a single game
    Game getGame(Team team1, Team team2) {
        SQLiteDatabase db = this.getReadableDatabase();
 
        Cursor cursor = db.query(TABLE_GAMES, new String[] { KEY_GAME_TEAM1POINTS, KEY_GAME_TEAM2POINTS, KEY_GAME_TOURNEY}, 
        		"(" + KEY_GAME_TEAM1 + "=?) AND (" + KEY_GAME_TEAM2 + "=?" + ")",
                new String[] { team1.getName(), team2.getName() }, null, null, null);
        
        if (cursor != null) cursor.moveToFirst();
        
        String tourneyName = cursor.getString(2);
        int t1Points = cursor.getInt(0);
        int t2Points = cursor.getInt(1);
        
        Game game = new Game(team1, team2, new Tournament(tourneyName), t1Points, t2Points);
        
        db.close();
        // return team
        return game;
    }
    
    // Getting all games from a tournament
    public ArrayList<Game> getTournamentGames(Tournament t) {
        ArrayList<Game> games = new ArrayList<Game>();
 
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_GAMES, new String[] {KEY_GAME_TEAM1, KEY_GAME_TEAM2, KEY_GAME_TEAM1POINTS, 
        		KEY_GAME_TEAM2POINTS}, "tournament =?", new String[] { t.getName() }, null, null, null);
 
        // Looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
            	String team1Name = cursor.getString(0);
            	String team2Name = cursor.getString(1);
            	int t1Points = cursor.getInt(2);
            	int t2Points = cursor.getInt(3);
            	
                Game game = new Game(new Team(team1Name), new Team(team2Name), t, 
                		t1Points, t2Points); 
                
                // Adding game to list
                games.add(game);
            } while (cursor.moveToNext());
        }
        
        db.close();

        return games;
    }
 
    // Updating single game
    public int updateGame(Game game) {
        SQLiteDatabase db = this.getWritableDatabase();
 
        ContentValues values = new ContentValues();
        values.put(KEY_GAME_TEAM1, game.getTeam1().getName()); // Team1 name
        values.put(KEY_GAME_TEAM2, game.getTeam2().getName()); // Team2 name
        values.put(KEY_GAME_TOURNEY, game.getTournament().getName()); // Tournament name
        values.put(KEY_GAME_TEAM1POINTS, game.getTeam1Points()); // Team1 points
        values.put(KEY_GAME_TEAM2POINTS, game.getTeam2Points()); // Team2 points
 
        db.close();
        // updating row
        return db.update(TABLE_GAMES, values, "(" + KEY_GAME_TEAM1 + " =?) AND (" + KEY_GAME_TEAM2 + "=?" + ")",
                new String[] { game.getTeam1().getName(), game.getTeam2().getName() });
    }
 
    // Deleting single game
    public void deleteGame(Game game) {
        SQLiteDatabase db = this.getWritableDatabase();
        
        db.delete(TABLE_GAMES, "(" + KEY_GAME_TEAM1 + " =?) AND (" + KEY_GAME_TEAM2 + "=?" + ")", 
        		new String[] { game.getTeam1().getName(), game.getTeam2().getName() });
        
        db.close();
    }
 
    // Getting games Count
    public int getGamesCount() {
    	SQLiteDatabase db = this.getReadableDatabase();
    	
    	String countQuery = "SELECT  * FROM " + TABLE_GAMES;
        Cursor cursor = db.rawQuery(countQuery, null);
        int count = cursor.getCount();
        cursor.close();
 
        db.close();
        return count;
    }
    
    /** Get the list of tournaments
     *  
     * @return An ArrayList of Tournament objects
     */
    public ArrayList<Tournament> getTournaments() {
    	SQLiteDatabase db = this.getReadableDatabase();
    	
    	String query = "SELECT DISTINCT " + KEY_GAME_TOURNEY + " FROM " + TABLE_GAMES;
    	Cursor cursor = db.rawQuery(query, null);
    	
    	ArrayList<Tournament> tourneyList = new ArrayList<Tournament>();
    	
    	if (cursor.moveToFirst()) {
    		do {
    			String tourneyName = cursor.getString(0);
    			Tournament t = new Tournament(tourneyName);
    			ArrayList<Game> games = getTournamentGames(t);
    			t.setTourneyGames(games);
    			tourneyList.add(t);
    			
    		} while (cursor.moveToNext());
    		
    	}
    	
    	return tourneyList;	
    }

}
